<?php
namespace Api\Model\Amazon;
use Think\Exception;

class ReportInventoryModel extends CommonModel{
    // 数据表前缀
    protected $tablePrefix   = 'api_';
    // 数据库配置
    protected $connection    = 'fbawarehouse';
    // 数据表名（不包含表前缀）
    protected $tableName     = 'report_inventory';

    protected $trueTableName = 'api_report_inventory';

    /**
     * @param $rowLists
     * @param $tableName
     * 描述：插入解析数据
     */
    public function insertData($tableName,$rowLists){

        /*$sql = 'SELECT `create_time` FROM `' . $tableName . '` ORDER BY `id` DESC LIMIT 1';
        $maxCreateTime = $this->query($sql);
        $maxCreateTime = !empty($maxCreateTime) ? $maxCreateTime[0]['create_time'] : '';
        if(!empty($maxCreateTime) && ((time() - strtotime($maxCreateTime)) > 86400 )) {
            $sql = 'TRUNCATE TABLE  `' . $tableName . '`';
            $this->query($sql);
        }*/

        /*if(!empty($rowLists)) {
            $rowListsChunks = array_chunk($rowLists, 500);
            foreach($rowListsChunks as $rowListsChunk) {
                $this->addAll($rowListsChunk);
            }
        }*/
        if(!empty($rowLists)) {
            foreach($rowLists as $row) {
                try {
                    $this->add($row);
                } catch(Exception $ex) {
                    continue;
                }
            }
        }
    }

    /**
     * @param array $options
     * @return bool|mixed
     * 描述：根据条件查询数据
     */
    public function selectData($options = array()){
        $data = $this->where($options)->select();

        return isset($data) ? $data : false;
    }

    /**
     * 计算库存
     */
    public function stockCalculate ($reportFileId) {
        if(empty($reportFileId))return false;

        $sqlSelect = 'SELECT `ri` . `account_id`,`skus` . `private_sku`,
                      SUM(`ri` . `quantity_for_local_fulfillment`) AS `stock`
                      FROM `api_report_inventory` AS `ri`
                      LEFT JOIN `api_account_seller_sku` AS `skus`
                      ON `skus` . `seller_sku` = `ri` . `sku`
                      AND `ri` . `account_id` = `skus` . `account_id`
                      WHERE `ri` . `account_id` != 0 
                      AND `ri` . `report_file_id` = ' . $reportFileId . ' ';

        $stockLists = $this->query($sqlSelect .
            ' GROUP BY `ri` . `account_id`, `skus` . `private_sku`');

        if(!empty($stockLists)){
            D('Api/Amazon/ReportStockSale')->insertInventoryData($stockLists);
        }

    }

    /**
     * @param $skus
     * @return array
     * 描述：根据SKU找出账号ID和可用库存
     */
    public function getInventoryBySellerSku($options) {
        /*if (!empty($skus)) {
            $inventory = array();
            $result = $this->field('account_id,sku,quantity_for_local_fulfillment AS inventory')
                ->where(array(
                    'sku' => array('IN',$skus),
                    'create_time' => array('LIKE' ,'%' . date('Y-m-d') . '%')
                    ))
                ->order('create_time desc')
                ->select();
            foreach ($result as $value) {
                if (!isset($inventory[$value['account_id'] . ':' .$value['sku']])) {
                    $inventory[$value['account_id'] . ':' .$value['sku']] = $value['inventory'];
                }
            }

            return !empty($inventory) ? $inventory : array();
        } else
            return array();*/
        $sql = 'SELECT `ari`.`account_id`,`as`.`shorthand_code`,`aa`.`name` AS accountName,`ari`.`sku`,`ari`.`asin`,
                `aass`.`private_sku`,`sst`.`item`,`aass`.`sale_status_id`,`ari`.`quantity_for_local_fulfillment` AS `inventory`
                FROM `api_report_inventory` AS `ari`
                LEFT JOIN `amazonorder_accounts` AS `aa` ON `aa`.`id` = `ari`.`account_id`
                LEFT JOIN `api_account_seller_sku` AS `aass` ON `aass`.`account_id` = `ari`.`account_id` AND `aass`.`seller_sku` = `ari`.`sku`
                LEFT JOIN `amazonorder_sites` AS `as` ON `aass`.`site_id` = `as`.`id`
                LEFT JOIN `skusystem_sku_taxrate` AS `sst` ON `sst`.`sku` = `aass`.`private_sku`
                WHERE `ari`.`account_id` != 0
                AND `aass`.`private_sku` IN (' . $options['sku'] . ')
                AND `ari`.`create_time` LIKE \'%' . date('Y-m-d') . '%\'
                ORDER BY `ari`.`create_time` DESC';

        $result = $this->query($sql);

        $inventory = array();

        foreach ($result as $key => $value) {
            if (!isset($inventory[$value['account_id'] . ':' .$value['sku']])) {
                $inventory[$value['account_id'] . ':' .$value['sku']] = $result[$key];
            }
        }

        return !empty($inventory) ? $inventory : array();
    }

    public function getSingleData() {
        $create_time = $this->field('LEFT(`create_time`,13) AS create_time')
            ->order('`create_time` DESC')
            ->limit(1)
            ->find();

        $sql = 'SELECT `ari`.`sku`,`as`.`shorthand_code`,`aass`.`private_sku`,`aa`.`name`,`ari`.`asin`,`ari`.`quantity_for_local_fulfillment` 
                FROM `api_report_inventory` as `ari` 
                LEFT JOIN `amazonorder_accounts` as `aa` 
                ON `ari`.`account_id` = `aa`.`id` 
                LEFT JOIN `api_account_seller_sku` as `aass` 
                ON `ari`.`account_id` = `aass`.`account_id` AND `ari`.`sku` = `aass`.`seller_sku` 
                LEFT JOIN `amazonorder_sites` as `as` ON `as`.`id` = `aass`.`site_id` 
                WHERE `ari`.`create_time` LIKE "' .$create_time['create_time'] .'%"';

        $data = $this->query($sql);

        $title = array(
            'sku' => 'SellerSKU',
            'shorthand_code' => '站点',
            'private_sku' => '公司SKU',
            'name' => '账号名',
            'asin' => 'ASIN',
            'quantity_for_local_fulfillment' => '库存'
        );
        $result['data'] = !empty($data) ? $data : array();
        $result['title'] = $title;
        $result['fileName'] = "实时库存报表";
        return $result;
    }
}